package com.seeyon.apps.exportExcelCtrl.units;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.seeyon.ctp.common.SystemEnvironment;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;


/**
 * @Title ExcelTest
 * @description: 表格导出测试类
 * @author wxt.daicheng
 * @date 2024/8/14 
 * @version 1.0
 */
public class ExcelUtils {
    public static File export(JSONObject alldata) throws IOException {


        String leader = alldata.getString("leader");
        // Excel表开始
        //创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        String[] sheetnameList = {"市领导","总经理","党委主要领导"};

        for (String sheetName : sheetnameList) {
            JSONObject data = alldata.getJSONObject(sheetName);
            if (data == null){
                data = new JSONObject();
                data.put("date",alldata.getString("date"));
                data.put("createUser",alldata.getString("createUser"));
                data.put("leader",leader);

            }
            createSheet(data,wb,sheetName);
        }

        //生成文件
        String fileName = SystemEnvironment.getSystemTempFolder() + File.separator + leader + ".xlsx";

        File file = new File(fileName);
        if (file.exists()){
            file.delete();
        }
        // 保存文件
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();

        return file;
    }

    private static void createSheet(JSONObject data, HSSFWorkbook wb,String sheetName) throws IOException {
        String createUser = data.getString("createUser");
        String leader = data.getString("leader");
        String date = data.getString("date");

        //添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        //设置宽度
        int[] width = {6, 10, 40, 20, 60, 50, 25,20,15,15,15,15};
        for (int columnIndex = 0; columnIndex < 12; columnIndex++) {
            sheet.setColumnWidth(columnIndex, width[columnIndex] * 256);
        }
        //设置样式集合
        Map<String, HSSFCellStyle> styles = addStyle(wb);

        int rowIndex = 4;
        int colIndex;
        //添加标题行
        HSSFRow row0 = sheet.createRow(0);
        // 设置行的高度为64像素
        row0.setHeight((short) (65 * 20));

        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellStyle(styles.get("title"));
        String cellValues0 = "";
        if (sheetName.equals("市领导")){
            cellValues0 = "市领导批示及市政府督办单落实情况月报表（" + leader + "同志条线）";
        }else if(sheetName.equals("总经理")){
            cellValues0 = "集团总经理批示及会议交办事项落实情况月报表（" + leader + "同志条线）";
        }else if(sheetName.equals("党委主要领导")){
            cellValues0 = "集团党委主要领导批示及会议交办事项落实情况月报表（" + leader + "同志条线）";
        }
        cell0.setCellValue(cellValues0);
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 11);
        sheet.addMergedRegion(cra);

        HSSFRow row1 = sheet.createRow(1);
        row1.setHeight((short) (33 * 20));
        for (int index = 0; index < 12; index++) {
            row1.createCell(index);
            row1.getCell(index).setCellStyle(styles.get("data_right"));
        }
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));
        HSSFCell row1Cell1 = row1.getCell(0);
        row1Cell1.setCellValue("制表人：" + createUser);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 11));
        row1.getCell(9).setCellValue("时间：" + date + "  ");

        String[] head = {"序\r\n号","办理类型","事项名称","交办\n日期","交办内容","落实情况","是否完成\r\n（必填项）","责任部门","责任人","责任\n领导","完成\n期限","备注"};
        HSSFRow row2 = sheet.createRow(2);
        row2.setHeight((short) (68 * 20));
        for (colIndex = 0; colIndex < 12; colIndex++) {
            row2.createCell(colIndex);
            row2.getCell(colIndex).setCellStyle(styles.get("header_center"));
            row2.getCell(colIndex).setCellValue(head[colIndex]);
        }

        HSSFRow row3 = sheet.createRow(3);
        row3.setHeight((short) (50 * 20));
        for (colIndex = 0; colIndex < 12; colIndex++) {
            row3.createCell(colIndex);
            row3.getCell(colIndex).setCellStyle(styles.get("header_left"));
        }
        JSONArray todo = data.getJSONArray("todo");
        if (todo == null){
            todo = new JSONArray();
        }
        CellRangeAddress cra3 = new CellRangeAddress(3, 3, 0, 11);
        sheet.addMergedRegion(cra3);
        HSSFCell row3Cell = row3.getCell(0);
        row3Cell.setCellValue("一、未完成且推进中（"+todo.size()+"项）");
        // 后面的数据可以根据自己的需求写....................

        //回填未完成事件
        if (!todo.isEmpty()){
            for (int i = 0; i < todo.size(); i++) {
                JSONArray todoRow = todo.getJSONArray(i);
                HSSFRow row = sheet.createRow(rowIndex);
                row.setHeight((short) (50 * 20));
                for (colIndex = 0; colIndex < 12; colIndex++) {
                    row.createCell(colIndex);
                    HSSFCell cell = row.getCell(colIndex);
                    cell.setCellStyle(styles.get("data_left"));
                    cell.setCellValue(todoRow.get(colIndex).toString());

                }
                rowIndex++;
            }
        }else {
            HSSFRow row = sheet.createRow(rowIndex);
            row.setHeight((short) (50 * 20));
            for (colIndex = 0; colIndex < 12; colIndex++) {
                row.createCell(colIndex);
                HSSFCell cell = row.getCell(colIndex);
                cell.setCellStyle(styles.get("data_left"));
                cell.setCellValue("");
            }
            rowIndex++;
        }

        //推进中标题栏
        HSSFRow promote = sheet.createRow(rowIndex);
        promote.setHeight((short) (50 * 20));
        for (colIndex = 0; colIndex < 12; colIndex++) {
            promote.createCell(colIndex);
            promote.getCell(colIndex).setCellStyle(styles.get("header_left"));
        }
        JSONArray promoteData = data.getJSONArray("promote");
        if (promoteData == null){
            promoteData = new JSONArray();
        }
        CellRangeAddress cra4 = new CellRangeAddress(rowIndex, rowIndex, 0, 11);
        sheet.addMergedRegion(cra4);
        HSSFCell row4Cell = promote.getCell(0);
        row4Cell.setCellValue("二、推进中（"+promoteData.size()+"项）");
        rowIndex++;
        //回填推进中事件
        if (!promoteData.isEmpty()){
            for (int i = 0; i < promoteData.size(); i++) {
                JSONArray promoteDataRow = promoteData.getJSONArray(i);
                HSSFRow row = sheet.createRow(rowIndex);
                row.setHeight((short) (50 * 20));
                for (colIndex = 0; colIndex < 12; colIndex++) {
                    row.createCell(colIndex);
                    HSSFCell cell = row.getCell(colIndex);
                    cell.setCellStyle(styles.get("data_left"));
                    cell.setCellValue(promoteDataRow.get(colIndex).toString());

                }
                rowIndex++;
            }
        }else {
            HSSFRow row = sheet.createRow(rowIndex);
            row.setHeight((short) (50 * 20));
            for (colIndex = 0; colIndex < 12; colIndex++) {
                row.createCell(colIndex);
                HSSFCell cell = row.getCell(colIndex);
                cell.setCellStyle(styles.get("data_left"));
                cell.setCellValue("");

            }
            rowIndex++;
        }

        //推进中标题栏
        HSSFRow end = sheet.createRow(rowIndex);
        end.setHeight((short) (50 * 20));
        for (colIndex = 0; colIndex < 12; colIndex++) {
            end.createCell(colIndex);
            end.getCell(colIndex).setCellStyle(styles.get("header_left"));
        }
        JSONArray endData = data.getJSONArray("end");
        if (endData == null){
            endData = new JSONArray();
        }
        CellRangeAddress cra5 = new CellRangeAddress(rowIndex, rowIndex, 0, 11);
        sheet.addMergedRegion(cra5);
        HSSFCell row5Cell = end.getCell(0);
        row5Cell.setCellValue("三、已完成（"+endData.size()+"项）");
        rowIndex++;
        //回填已完成事件
        if (!endData.isEmpty()){
            for (int i = 0; i < endData.size(); i++) {
                JSONArray endDataRow = endData.getJSONArray(i);
                HSSFRow row = sheet.createRow(rowIndex);
                row.setHeight((short) (50 * 20));
                for (colIndex = 0; colIndex < 12; colIndex++) {
                    row.createCell(colIndex);
                    HSSFCell cell = row.getCell(colIndex);
                    cell.setCellStyle(styles.get("data_left"));
                    cell.setCellValue(endDataRow.get(colIndex).toString());

                }
                rowIndex++;
            }
        }else {
            HSSFRow row = sheet.createRow(rowIndex);
            row.setHeight((short) (50 * 20));
            for (colIndex = 0; colIndex < 12; colIndex++) {
                row.createCell(colIndex);
                HSSFCell cell = row.getCell(colIndex);
                cell.setCellStyle(styles.get("data_left"));
                cell.setCellValue("");
            }
            rowIndex++;
        }
    }

    private static Map<String, HSSFCellStyle> addStyle(HSSFWorkbook wb) {
        @SuppressWarnings({"unchecked", "rawtypes"})
        Map<String, HSSFCellStyle> styles = new HashMap();

        //设置字体
        HSSFFont headFont = wb.createFont();
        headFont.setFontName("方正小标宋_GBK");
        headFont.setFontHeightInPoints((short) 36);
//        headFont.setBold(true);
        HSSFFont bodyFont = wb.createFont();
        bodyFont.setFontName("方正小标宋_GBK");
        bodyFont.setFontHeightInPoints((short) 18);

        //标题行样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(headFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        styles.put("title", style);

        //数据头居中样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        styles.put("header_center", style);

        //数据头居左样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        styles.put("header_left", style);

        //数据行居中样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        styles.put("data_center", style);

        //数据行居中底色样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
        styles.put("data_center_color", style);

        //数据行居中底色样式2
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
        styles.put("data_center_color1", style);

        //数据行居左样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        styles.put("data_left", style);

        //数据行居右样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setAlignment(HorizontalAlignment.RIGHT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        styles.put("data_right", style);
        //无边框样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        styles.put("data_noborder", style);
        //无底边框样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        styles.put("data_bottom", style);

        return styles;
    }

    /**
     * @title compressFiles
     * @description: 多个文件打包成压缩包
     * @author wxt.daicheng
     * @param files: 需要打包的文件
     * @param zipFile:  打包后的压缩包
     * @date: 2024/8/22
     * @return: void
     */
    public static void compressFiles(List<File> files, File zipFile) throws IOException {
        try (ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(zipFile))) {
            for (File file : files) {
                if (file.exists()) {
                    FileInputStream fis = new FileInputStream(file);
                    ZipEntry zipEntry = new ZipEntry(file.getName());
                    zos.putNextEntry(zipEntry);

                    byte[] bytes = new byte[1024];
                    int length;
                    while ((length = fis.read(bytes)) >= 0) {
                        zos.write(bytes, 0, length);
                    }

                    zos.closeEntry();
                    fis.close();
                }
            }
        }
    }
}
